﻿using Biotech.Log;
using Microsoft.Data.Sqlite;
using Microsoft.Win32;
using NPOI.HSSF.Record;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using project.B12.application.Ioc;
using project.B12.IRepository;
using project.B12.IRepository.Base;
using project.B12.IService;
using project.B12.Model.Enums;
using project.B12.Model.Models;
using project.B12.Model.Res;
using project.B12.Repository.Repository;
using project.B12.Service;
using software.Common.MVVM;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;
using static NPOI.HSSF.Util.HSSFColor;

namespace project.B12.application.ViewModels.Setting
{
    public class ExcelExportViewModel : ObservableObject
    {
        private IMedicineRepository _medicineRepository;
        private IWorkSequence _workSequence;
        private ISIRRepository _sIRRepository;
        private IBacteriaRepository _bacteriaRepository;
        private readonly IUnitOfWork _unitOfWork;
        private string _filePath;
        private readonly string pattern = @"[^\w\s]";

        public string FilePath
        {
            get => _filePath;
            set
            {
                _filePath = value;
                this.RaisePropertyChanged();
            }
        }

        private string _fileName;
        public string FileName
        {
            get => _fileName;
            set
            {
                _fileName = value;
                this.RaisePropertyChanged();
            }
        }

        private List<string> _sheetNames;
        public List<string> SheetNames
        {
            get => _sheetNames;
            set
            {
                _sheetNames = value;
                this.RaisePropertyChanged();
            }
        }

        private string _sheetName;
        public string SheetName
        {
            get { return _sheetName; }
            set
            {
                _sheetName = value;
                if (value == "细菌")
                    TableName = "TB_bacteria";
                else if (value == "药物")
                    TableName = "TB_medicine";
                else if (value == "SIR-MIC")
                    TableName = "TB_SIR";
                else
                {
                    TableName = "暂不支持导入";
                    IsExportEnabled = false;
                }
                this.RaisePropertyChanged();
            }
        }

        private int _index = 0;
        public int Index
        {
            get => _index;
            set
            {
                _index = value;
                this.RaisePropertyChanged();
            }
        }

        private bool _isChecked = true;
        public bool IsChecked
        {
            get { return _isChecked; }
            set
            {
                _isChecked = value;
                this.RaisePropertyChanged();
            }
        }

        private List<string> _tableNames;
        public List<string> TableNames
        {
            get { return _tableNames; }
            set
            {
                _tableNames = value;
                this.RaisePropertyChanged();
            }
        }

        private string _tableName;
        public string TableName
        {
            get { return _tableName; }
            set
            {
                _tableName = value;
                this.RaisePropertyChanged();
            }
        }

        private bool _isLoadEnabled = true;
        public bool IsLoadEnabled
        {
            get { return _isLoadEnabled; }
            set
            {
                _isLoadEnabled = value;
                this.RaisePropertyChanged();
            }
        }

        private bool _isExportEnabled;
        public bool IsExportEnabled
        {
            get { return _isExportEnabled; }
            set
            {
                _isExportEnabled = value;
                this.RaisePropertyChanged();
            }
        }

        public RelayCommand Load => new RelayCommand(LoadExcel);

        public RelayCommand Export => new RelayCommand(ExportExcelToDB);

        public ExcelExportViewModel(IMedicineRepository medicineRepository,
            IWorkSequence workSequence,
            ISIRRepository sIRRepository,
            IBacteriaRepository bacteriaRepository,
            IUnitOfWork unitOfWork)
        {
            _medicineRepository = medicineRepository;
            _workSequence = workSequence;
            _sIRRepository = sIRRepository;
            _bacteriaRepository = bacteriaRepository;
            _unitOfWork = unitOfWork;
            GetTableNames();
        }

        private List<string> GetTableNames()
        {
            TableNames = new List<string>();
            TableNames.Add("暂不支持导入");
            using (var connection = Connect())
            {
                string query = "SELECT name FROM sqlite_master WHERE type='table';";
                using (var command = new SqliteCommand(query, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (reader.GetString(0) == "sqlite_sequence")
                                continue;
                            TableNames.Add(reader.GetString(0));
                        }
                    }
                }
            }
            return TableNames;
        }

        private SqliteConnection Connect()
        {
            var dbConnect = new SqliteConnection(DataPath.DataConnectionString);
            dbConnect.Open();
            return dbConnect;
        }

        private void LoadExcel()
        {
            List<string> data = new List<string>();
            try
            {
                IsLoadEnabled = false;
                OpenFileDialog openFileDialog = new OpenFileDialog
                {
                    Title = "选择一个文件",
                    Filter = "Excel工作表(*.xlsx)|*.xlsx"
                };

                if (openFileDialog.ShowDialog() == true)
                    FilePath = openFileDialog.FileName;
                else
                    return;

                IWorkbook workbook = null;
                using (FileStream fs = System.IO.File.OpenRead(FilePath))
                {
                    // 2007版本  
                    if (_filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (_filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);
                    if (workbook == null)
                    {
                        MessageBox.Show("该表内容为空！");
                        return;
                    }
                    int sheetCount = workbook.NumberOfSheets;
                    for (int i = 0; i < sheetCount; i++)
                    {
                        ISheet sheet = workbook.GetSheetAt(i);
                        data.Add(sheet.SheetName);
                    }
                }
            }
            catch
            {
                MessageBox.Show("当前工作表未关闭！");
                return;
            }
            finally
            {
                IsLoadEnabled = true;
            }
            int index = FilePath.LastIndexOf("\\");
            FileName = FilePath.Substring(index + 1);
            SheetNames = data;
            IsExportEnabled = true;
        }

        private void ExportExcelToDB()
        {
            try
            {
                IsExportEnabled = false;
                var dataTable = ReadExcelToDataSet(FilePath, Index);
                if (dataTable == null)
                    return;

                //_unitOfWork.DropDB(TableName);

                if (TableName == "TB_SIR")
                {
                    ExportSIR(dataTable);
                    return;
                }
                if (TableName == "TB_medicine")
                {
                    ExportMedicine(dataTable);
                    return;
                }
                if(TableName== "TB_bacteria")
                {
                    ExportBacteria(dataTable);
                }
            }
            catch (Exception ex)
            {
                LoggerFactory.Instance.CurrentLogger.LogInfo(ex.ToString(), "", "软件");
            }
            finally
            {
                IsExportEnabled = true;
            }
        }

        private void ExportSIR(DataTable dataTable)
        {
            Dictionary<int, string> records = new Dictionary<int, string>();
            List<Table_medicine> medicines = GetMedicines();
            int exportCount = 0;
            try
            {
                var bacterias = _bacteriaRepository.QueryAllBacteria();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    //根据英文简称得到对应的药物
                    string abbrev = dataTable.Rows[i][3].ToString();
                    string medicineAbbrev=abbrev.Replace("_NM", ""); ;
                    var medicinesByAbbrev = _medicineRepository.QueryByAbbreviation(medicineAbbrev);
                    if (medicinesByAbbrev.Count == 0||medicinesByAbbrev == null)
                        continue;
                    Table_medicine medicine = medicinesByAbbrev.Where(o => o.Abbreviation == medicineAbbrev).First();

                    string levelName = dataTable.Rows[i][1].ToString();
                    BacteriaLevel level = new BacteriaLevel();
                    switch (levelName)
                    {
                        case "界":
                            level = BacteriaLevel.界; break;
                        case "门":
                            level = BacteriaLevel.门; break;
                        case "纲":
                            level = BacteriaLevel.纲; break;
                        case "目":
                            level = BacteriaLevel.目; break;
                        case "科":
                            level = BacteriaLevel.科; break;
                        case "属":
                            level = BacteriaLevel.属; break;
                        case "种":
                            level = BacteriaLevel.种; break;
                        default:
                            level = BacteriaLevel.种; break;
                    }
                    string bacName = dataTable.Rows[i][2].ToString();
                    var temp = bacterias.Where(o => o.CN_Name == bacName).ToList();
                    int id = 0;
                    if (temp == null || temp.Count() == 0)
                    {
                        id = _bacteriaRepository.QueryIdByLevelName(level, bacName);
                        if (id == 0)
                            continue;
                    }
                    else
                    {
                        id = temp.FirstOrDefault().Id;
                    }

                    var sirTemp = _sIRRepository.QueryByMedicineId(medicine.Id);
                    string source = dataTable.Rows[i][8].ToString();
                    if (sirTemp.Any(o=>o.BacteriaLevel== level&&o.BacteriaLevelID==id&&o.Source== source))
                    {
                        records.Add(i + 1, bacName);
                        continue;
                    }
                    int errorCount = 0;
                    for (int j = 1; j < dataTable.Columns.Count; j++)
                    {
                        if (string.IsNullOrEmpty(dataTable.Rows[i][j].ToString()))
                        {
                            errorCount++;
                            if (errorCount == dataTable.Columns.Count - 1)
                            {
                                MessageBox.Show($"导入完成，已成功导入{exportCount}行！");
                                return;
                            }
                        }
                    }
                    Table_SIR sir = new Table_SIR()
                    {
                        BacteriaLevel = level,
                        BacteriaLevelID = id,
                        MedicineID = medicine.Id,
                        S = dataTable.Rows[i][4].ToString(),
                        I = dataTable.Rows[i][5].ToString(),
                        R = dataTable.Rows[i][6].ToString(),
                        Source = source
                    };
                    _sIRRepository.AddSIR(sir);
                }
                ShowTip(exportCount, records);
            }
            finally
            {
                ViewModelLocator.SIRViewModel.Init();
            }
        }

        private List<Table_medicine> GetMedicines()
        {
            List<Table_medicine> medicines = new List<Table_medicine>();
            var medicinesTemp = _medicineRepository.QueryAllMedicine();
            foreach(var medicine in medicinesTemp)
            {
                var temp = Regex.Replace(medicine.CN_Name, pattern, "");
                medicine.CN_Name = temp;
                medicines.Add(medicine);
            }
            return medicines;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="count">导入的行数</param>
        /// <param name="records">重复的记录</param>
        private void ShowTip(int count,Dictionary<int,string> records)
        {
            MessageBox.Show($"导入完成,已成功导入{count + 1}行");
            IsExportEnabled = true;
            return;
            if (records.Count == 0)
            {
                MessageBox.Show($"导入完成,已成功导入{count + 1}行");
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                foreach (var record in records)
                {
                    sb.Append($"第{record.Key}行{record.Value}重复\n");
                }
                MessageBox.Show(sb.ToString());
            }
            IsExportEnabled = true;
        }

        /// <summary>
        /// 简称唯一
        /// </summary>
        /// <param name="dataTable"></param>
        private void ExportMedicine(DataTable dataTable)
        {
            int exportCount = 0;
            Dictionary<int, string> records = new Dictionary<int, string>();
            var medicines = _medicineRepository.QueryAllMedicine();
            try
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    string medicineName = null;
                    string englishName = null;
                    string abbrev = null;
                    bool delete = false;
                    int errorCount = 0;
                    for (int j = 1; j < dataTable.Columns.Count; j++)
                    {
                        if (string.IsNullOrEmpty(dataTable.Rows[i][j].ToString()))
                        {
                            errorCount++;
                            if (errorCount == dataTable.Columns.Count - 1)
                            {
                                MessageBox.Show($"导入完成，已成功导入{exportCount}行！");
                                return;
                            }
                        }
                        string column = dataTable.Rows[i][j].ToString();
                        switch (j)
                        {
                            case 1:
                                medicineName = column;
                                break;
                            case 2:
                                englishName = column;
                                break;
                            case 3:
                                abbrev = column;
                                break;
                            case 4:
                                delete = column == "0" ? false : true;
                                break;
                        }
                    }
                    if (!abbrev.Contains("_NM"))
                        continue;
                    abbrev = abbrev.Replace("_NM", "");
                    if (medicines.Any(o => o.Abbreviation == abbrev))
                    {
                        records.Add(i + 1, abbrev);
                        continue;
                    }
                    Table_medicine medicine = new Table_medicine()
                    {
                        CN_Name = medicineName,
                        EN_Name = englishName,
                        Abbreviation = abbrev,
                        IsDelete = delete
                    };
                    _medicineRepository.AddMedicine(medicine);
                    exportCount++;
                }
                ShowTip(exportCount, records);
            }
            finally
            {
                ViewModelLocator.MedicineViewModel.Init();
            }
        }

        /// <summary>
        /// 英文名唯一
        /// </summary>
        /// <param name="dataTable"></param>
        private void ExportBacteria(DataTable dataTable)
        {
            Dictionary<int,string> records= new Dictionary<int,string>();
            int exportCount = 0;
            try
            {
                var bacterias = _bacteriaRepository.QueryAllBacteria();
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    string name = string.Empty;
                    string englishName = string.Empty;
                    string abbrev = string.Empty;
                    string _kingdom = string.Empty;
                    string _phylum = string.Empty;
                    string _class = string.Empty;
                    string _order = string.Empty;
                    string _family = string.Empty;
                    string _genus = string.Empty;
                    string _species = string.Empty;
                    int read = 1;
                    string remarks = string.Empty;
                    bool delete = false;

                    int errorCount = 0;
                    for (int j = 1; j < dataTable.Columns.Count; j++)
                    {
                        if (string.IsNullOrEmpty(dataTable.Rows[i][j].ToString()))
                        {
                            errorCount++;
                            if (errorCount == dataTable.Columns.Count - 1)
                            {
                                MessageBox.Show($"导入完成，已成功导入{exportCount}行！");
                                return;
                            }
                        }

                        string column = dataTable.Rows[i][j].ToString();
                        switch (j)
                        {
                            case 1:
                                name = column;
                                break;
                            case 2:
                                englishName = column;
                                break;
                            case 3:
                                abbrev = column;
                                break;
                            case 4:
                                _kingdom = column;
                                break;
                            case 5:
                                _phylum = column;
                                break;
                            case 6:
                                _class = column;
                                break;
                            case 7:
                                _order = column;
                                break;
                            case 8:
                                _family = column;
                                break;
                            case 9:
                                _genus = column;
                                break;
                            case 10:
                                _species = column;
                                break;
                            case 11:
                                read = int.Parse(column);
                                break;
                            case 12:
                                remarks = column;
                                break;
                            case 13:
                                delete = column == "0" ? false : true;
                                break;
                        }
                    }

                    if (bacterias.Any(o => o.EN_Name == englishName))
                    {
                        records.Add(i + 1, name);
                        continue;
                    }

                    Table_bacteria bacteria = new Table_bacteria()
                    {
                        CN_Name = name,
                        EN_Name = englishName,
                        Abbreviation = abbrev,
                        Kingdom = _kingdom,
                        Phylum = _phylum,
                        Class = _class,
                        Order = _order,
                        Family = _family,
                        Genus = _genus,
                        Species = _species,
                        Read = read,
                        Remarks = remarks,
                        IsDelete = delete
                    };
                    _bacteriaRepository.AddBacterias(bacteria);
                }
                ShowTip(exportCount, records);
            }
            finally
            {
                ViewModelLocator.BacteriaViewModel.Init();
            }
        }

        private DataTable ReadExcelToDataSet(string filePath, int index)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            int cc = 0;
            try
            {
                using (fs = System.IO.File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    sheet = workbook.GetSheetAt(index);//读取第一个sheet，当然也可以循环读取每个sheet  
                    dataTable = new DataTable();
                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//总行数
                        if (rowCount <= 0)
                        {
                            MessageBox.Show("该表内容为空！");
                            return null;
                        }

                        IRow firstRow = sheet.GetRow(0);//第一行  
                        int cellCount = firstRow.LastCellNum;//列数  

                        //构建datatable的列  
                        if (IsChecked)
                        {
                            startRow = 1;//如果第一行是列名，则从第二行开始读取  
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                cell = firstRow.GetCell(i);
                                if (cell != null)
                                {
                                    if (cell.StringCellValue != null)
                                    {
                                        column = new DataColumn(cell.StringCellValue);
                                        dataTable.Columns.Add(column);
                                    }
                                }
                            }
                        }
                        else
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                column = new DataColumn("column" + (i + 1));
                                dataTable.Columns.Add(column);
                            }
                        }

                        //填充行  
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            cc = i;
                            row = sheet.GetRow(i);
                            if (row == null) continue;

                            dataRow = dataTable.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    dataRow[j] = "";
                                }
                                else
                                {
                                    //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank:
                                            dataRow[j] = "";
                                            break;
                                        case CellType.Numeric:
                                            short format = cell.CellStyle.DataFormat;
                                            //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                            if (format == 14 || format == 31 || format == 57 || format == 58)
                                                dataRow[j] = cell.DateCellValue;
                                            else
                                                dataRow[j] = cell.NumericCellValue;
                                            break;
                                        case CellType.String:
                                            dataRow[j] = cell.StringCellValue;
                                            break;
                                    }
                                }
                            }
                            dataTable.Rows.Add(dataRow);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                var a = cc;
                if (fs != null)
                {
                    fs.Close();
                }
                throw ex;
            }
            return dataTable;
        }
    }
}
